/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.football.lottery.dao;

import com.football.lottery.obj.FootballLottery;
import com.football.lottery.obj.GameItem;
import com.football.lottery.obj.RecommendedItem;
import com.football.lottery.util.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.openide.util.Exceptions;

/**
 *
 * @author JingWen
 */
public class FootballLotteryDAOImpl implements FootballLotteryDAO
{
  private Connection getConnection()
  {
    return JDBCUtil.getPGConnection();
  }
  
  @Override
  public List getAllFootballLottery()
  {
    Connection conn = this.getConnection();
    try
    {
      String sql = "select * from footballlottery order by periodno DESC;";
      PreparedStatement pst = conn.prepareStatement(sql);
      ResultSet rs = pst.executeQuery();
      
      List result = new ArrayList();
      while(rs.next())
      {
        FootballLottery fbl = new FootballLottery();
        fbl.setPeriodNo(rs.getInt(1));
        fbl.setStopTime(rs.getString(2));
        fbl.setResultStr(rs.getString(3));
        fbl.setBetAmount(rs.getDouble(4));
        result.add(fbl);
      }
      
      Iterator it = result.iterator();
      while (it.hasNext())
      {
        FootballLottery fbl = (FootballLottery) it.next();
        int periodNo = fbl.getPeriodNo();
        fbl.setGames(this.getGamesByPeriodNo(periodNo, conn));
      }
      
      return result;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      return null;
    }
    finally
    {
      JDBCUtil.closeConnection(conn);
    }
    
  }

  @Override
  public boolean insertFootballLottery(FootballLottery fbl)
  {
    Connection conn = this.getConnection();
    this.insertFootballLottery(fbl, conn);
    JDBCUtil.closeConnection(conn);
    return true;
  }
  
  public boolean insertFootballLottery(FootballLottery fbl, Connection conn)
  {
    try
    {
      String sql = "insert into footballlottery values (?, ?, ?, ?)";
      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, fbl.getPeriodNo());
//      pst.setString(2, new String(fbl.getStopTime().getBytes("GBK"), "UTF-8"));
      pst.setString(2, fbl.getStopTime());
      pst.setString(3, fbl.getResultStr());
      pst.setDouble(4, fbl.getBetAmount());
      
      int rs = pst.executeUpdate();
      boolean  insertGames = this.insertGames(fbl.getGames(), conn);
      
      return (rs > 0) && insertGames;
    }
    catch (Exception ex)
    {
      Exceptions.printStackTrace(ex);
      return false;
    }
  }
  
  public boolean insertFootballLottery(List fblList)
  {
    boolean isSuccess = false;
    Connection conn = this.getConnection();
    for (Object obj : fblList)
    {
      FootballLottery fbl = (FootballLottery) obj;
      isSuccess = this.insertFootballLottery(fbl, conn);
    }
    JDBCUtil.closeConnection(conn);
    return isSuccess;
  }

  @Override
  public boolean updateLotteryResult(FootballLottery fbl)
  {
    boolean result = false;
    int updateResult = -1;
    try
    {
      Connection conn = this.getConnection();
      String sql = "update footballlottery set result = ? where periodNo = ?";
      
      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, fbl.getResultStr());
      pst.setInt(2, fbl.getPeriodNo());
      updateResult = pst.executeUpdate();
      
      result = true;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      result = false;
    }
    return result && (updateResult > 0);
  }

  @Override
  public FootballLottery getFootballLotteryByPeriodNO(int periodNo)
  {
    Connection conn = this.getConnection();
    try
    {
      String sql = "select * from footballlottery where periodNo = ?;";
      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, periodNo);
      ResultSet rs = pst.executeQuery();
      
      FootballLottery fbl = null;
      if (rs.next())
      {
        fbl = new FootballLottery();
        fbl.setPeriodNo(rs.getInt(1));
        fbl.setStopTime(rs.getString(2));
        fbl.setResultStr(rs.getString(3));
        fbl.setBetAmount(rs.getDouble(4));
        fbl.setGames(this.getGamesByPeriodNo(periodNo, conn));
      }
      return fbl;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      return null;
    }
    finally
    {
      JDBCUtil.closeConnection(conn);
    }
  }

  @Override
  public List getGamesByPeriodNo(int periodNo)
  {
    Connection conn = this.getConnection();
    
    List games = this.getGamesByPeriodNo(periodNo, conn);
    
    JDBCUtil.closeConnection(conn);
    
    return games;
  }

  @Override
  public Map getAllGames()
  {
    throw new UnsupportedOperationException("Not supported yet.");
  }
  
  public boolean insertGames(List games)
  {
    try
    {
      Connection conn = this.getConnection();
      boolean result = this.insertGames(games, conn);
      JDBCUtil.closeConnection(conn);
      return result;
    }
    catch (Exception ex)
    {
      Exceptions.printStackTrace(ex);
      return false;
    }
  }
  
  private boolean insertGames(List games, Connection conn) throws Exception
  {
    Statement statement = null;
    boolean result = false;
    try
    {
      statement = conn.createStatement();
      conn.setAutoCommit(false);
      
      for (Object obj : games)
      {
        GameItem game = (GameItem) obj;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        //String gameType = new String(game.getGameType().getBytes("GBK"), "UTF-8");
        //String hostName = new String(game.getHostName().getBytes("GBK"), "UTF-8");
        //String scoreLine = game.getScoreline() == null ? "" : new String(game.getScoreline().getBytes("GBK"), "UTF-8");
        //String guestName = new String(game.getGuestName().getBytes("GBK"), "UTF-8");
        String guestName = game.getGuestName();
        
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("insert into game values (");
        sqlBuffer.append(game.getPeriodNo()).append(", ");
        sqlBuffer.append(game.getOrderNo()).append(", ");
        sqlBuffer.append("'").append(game.getGameType()).append("', ");
        //sqlBuffer.append("'").append(gameType).append("', ");
        sqlBuffer.append("'").append(df.format(game.getGameTime())).append("', ");
        sqlBuffer.append("'").append(game.getHostName()).append("', ");
        sqlBuffer.append(game.getScoreline() == null ? "''" : "'" + game.getScoreline()).append("', ");
        sqlBuffer.append("'").append(game.getGuestName()).append("', ");
        //sqlBuffer.append("'").append(hostName).append("', ");
        //sqlBuffer.append("'").append(scoreLine).append("', ");
        //sqlBuffer.append("'").append(guestName).append("', ");
        sqlBuffer.append("'").append(game.getCompensationRate()).append("', ");
        if (game.getBet() == null)
        {
          sqlBuffer.append("null, ");
        }
        else
        {
          sqlBuffer.append("'").append(game.getBet()).append("', ");
        }
        if (game.getPreventiveBet() == null)
        {
          sqlBuffer.append("null");
        }
        else
        {
          sqlBuffer.append("'").append(game.getPreventiveBet()).append("'");
        }
        sqlBuffer.append(")");
        System.out.println(sqlBuffer.toString());
        statement.addBatch(sqlBuffer.toString());
      }
      statement.executeBatch();
      conn.commit();
      result = true;
    }
    catch (Exception ex)
    {
      Exceptions.printStackTrace(ex);
      result = false;
      try
      {
        conn.rollback();
      }
      catch (SQLException ex1)
      {
        Exceptions.printStackTrace(ex1);
        throw ex;
      }
    }
    finally
    {
      try
      {
        statement.close();
      }
      catch (SQLException ex)
      {
        Exceptions.printStackTrace(ex);
      }
    }
    return result;
  }

  private List getGamesByPeriodNo(int periodNo, Connection conn)
  {
    try
    {
      String sql = "select * from game where periodNo = ?";
      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, periodNo);
      ResultSet rs = pst.executeQuery();
      
      List games = new ArrayList();
      while (rs.next())
      {
        GameItem game = new GameItem();
        
        game.setPeriodNo(rs.getInt(1));
        game.setOrderNo(rs.getInt(2));
        game.setGameType(rs.getString(3));
        game.setGameTime(rs.getTimestamp(4));
        game.setHostName(rs.getString(5));
        game.setScoreline(rs.getString(6));
        game.setGuestName(rs.getString(7));
        game.setCompensationRate(rs.getString(8));
        game.setBet(rs.getString(9));
        game.setPreventiveBet(rs.getString(10));
        
        games.add(game);
      }
      return games;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      return null;
    }
  }

  @Override
  public List getAllPeriodNo()
  {
    try
    {
      Connection conn = this.getConnection();
      String sql = "select periodNo from footballlottery order by periodNo desc";
      PreparedStatement pst = conn.prepareStatement(sql);
      ResultSet rs = pst.executeQuery();
      
      int size = rs.getFetchSize();
      List periodNos = new ArrayList(size);
      
      while (rs.next())
      {
        periodNos.add(new Integer(rs.getInt(1)));
      }
      
      JDBCUtil.closeConnection(conn);
      return periodNos;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      return new ArrayList(0);
    }
  }

  public boolean updateGames(List newGameItems)
  {
    boolean r = false;
    Connection conn = null;
    try
    {
      conn = this.getConnection();
      Statement stm = conn.createStatement();
      conn.setAutoCommit(false);
      Iterator it = newGameItems.iterator();
      while (it.hasNext())
      {
        GameItem game = (GameItem) it.next();
        StringBuffer sql = new StringBuffer();
        sql.append("update game ");
        sql.append("set bet = '").append(game.getBet()).append("', ");
        sql.append("preventiveBet = '").append(game.getPreventiveBet()).append("' ");
        sql.append("where periodno = ").append(game.getPeriodNo());
        sql.append(" and ");
        sql.append("orderno = ").append(game.getOrderNo());

        System.out.println(sql.toString());
        stm.addBatch(sql.toString());
      }
      stm.executeBatch();
      conn.commit();
      r = true;
    }
    catch (SQLException ex)
    {
      try
      {
        conn.rollback();
        r = false;
        Exceptions.printStackTrace(ex);
      }
      catch (SQLException ex1)
      {
        Exceptions.printStackTrace(ex1);
      }
    }
    finally
    {
      JDBCUtil.closeConnection(conn);
      return r;
    }
  }

  public void updateBetAmount(Integer periodNo, double betAmount)
  {
    try
    {
      Connection conn = this.getConnection();
      String sql = "update footballlottery set betamount = ? where periodNo = ?";

      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setDouble(1, betAmount);
      pst.setInt(2, periodNo);
      pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
    }
  }

  @Override
  public boolean updateLotteryResult(Map resultMap)
  {
    Connection conn = null;
    try
    {
      conn = this.getConnection();
      Statement stm = conn.createStatement();
      
      Iterator it = resultMap.entrySet().iterator();
      while (it.hasNext())
      {
        Entry entry = (Entry) it.next();
        int periodNo = (Integer) entry.getKey();
        String resultStr = (String) entry.getValue();
        String sql = "update footballlottery set resultStr = '" + resultStr + "' " + "where periodNo = " + periodNo;
        stm.addBatch(sql);
      }
      stm.executeBatch();
      return true;
    }
    catch (SQLException ex)
    {
      Exceptions.printStackTrace(ex);
      return false;
    }
  }

  @Override
  public boolean addRecommendedItems(List items)
  {
    Connection conn = null;
    try
    {
      conn = this.getConnection();
      Statement stm = conn.createStatement();
      
      Iterator it = items.iterator();
      while (it.hasNext())
      {
        RecommendedItem item = (RecommendedItem) it.next();
        StringBuffer sql = new StringBuffer();
        sql.append("insert into recommended ");
        sql.append("values(");
        sql.append(item.getPeriodNo()).append(", ");
        sql.append("'").append(item.getCameFrom()).append("', ");
        sql.append("'").append(item.getAuthor()).append("',");
        sql.append("'").append(item.getData()).append("', ");
        sql.append(item.getAmount());
        sql.append(")");
        
        stm.addBatch(sql.toString());
      }
      stm.executeBatch();
      return true;
    }
    catch (SQLException e)
    {
      Exceptions.printStackTrace(e);
      return false;
    }
  }
}
